﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DomainModel;  // must add this...
using System.Configuration; // must add this...
using System.Data.SqlClient; // must add this...
using System.Data; // must add this...

namespace DAL
{
    public static class DalCustomers
    {
        static string connection_string = ConfigurationSettings.AppSettings["dsn"];

        //this procedure inserts a customer
        //this procedure inserts a customer
        public static void InsertCustomer(BaseCustomer customer, ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            try
            {
                string strSQL = "insert_customer";

                //     conn.Open();

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                // mySA.SelectCommand.Parameters.Add(new SqlParameter("@customer_id", SqlDbType.VarChar, 50));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@email", SqlDbType.VarChar, 50));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@first_name", SqlDbType.VarChar, 50));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@last_name", SqlDbType.VarChar, 50));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@address", SqlDbType.VarChar, 50));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@state", SqlDbType.VarChar, 50));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@zip", SqlDbType.Int));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@city", SqlDbType.VarChar, 50));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@webmaster", SqlDbType.Int));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@blockuser", SqlDbType.Int));

                //  mySA.SelectCommand.Parameters["@customer_id"].Value = customer.customer_id;
                mySA.SelectCommand.Parameters["@email"].Value = customer.email;
                mySA.SelectCommand.Parameters["@first_name"].Value = customer.first_name;
                mySA.SelectCommand.Parameters["@last_name"].Value = customer.last_name;
                mySA.SelectCommand.Parameters["@address"].Value = customer.address;
                mySA.SelectCommand.Parameters["@state"].Value = customer.state;
                mySA.SelectCommand.Parameters["@zip"].Value = customer.zip;
                mySA.SelectCommand.Parameters["@city"].Value = customer.city;
                mySA.SelectCommand.Parameters["@webmaster"].Value = customer.webmaster;
                mySA.SelectCommand.Parameters["@blockuser"].Value = customer.blockuser;


                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

            }
            catch (Exception e)
            {

                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
        }

        /*
         BlockCustomer: Function that only webmaster use. this function is blocking bad user
         * Pram: id: user
         *        error: error or not ?
         *        
     
         */
        public static void BlockCustomer(int id, ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);

            try
            {
                string strSQL = "block_customer";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));

                mySA.SelectCommand.Parameters["@id"].Value = id;
                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
        }
        /*
        BlockCustomer: Function that only webmaster use. this function is blocking bad user
        * Pram: id: user
        *        error: error or not ?
        */
        public static void UnBlockCustomer(int id, ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);

            try
            {
                string strSQL = "unblock_customer";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));

                mySA.SelectCommand.Parameters["@id"].Value = id;
                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
        }

        /*
          ChangePermissions
         * method has function that able or disable webmaster function
         * parm: id: trager that we want to be able or disable webmaster fucntiuon 
         *        decisions: 0 : disable web master function
         *                   1 : able web master function 
         */
        public static void ChangePermissions(int id, int decisions, ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);

            try
            {
                string strSQL = "change_permissions";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@permission", SqlDbType.Int));



                mySA.SelectCommand.Parameters["@id"].Value = id;
                mySA.SelectCommand.Parameters["@permission"].Value = decisions;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
        }


        /*
         * GetId : getting user id based on first and last name 
         * 
         */

        public static BaseCustomer getId(BaseCustomer customer, ref List<string> errors)
        {

            SqlConnection conn = new SqlConnection(connection_string);
            BaseCustomer customers = new BaseCustomer();

            try
            {
                string strSQL = "get_customer_id";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@first_name", SqlDbType.VarChar, 50));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@last_name", SqlDbType.VarChar, 50));

                mySA.SelectCommand.Parameters["@first_name"].Value = customer.first_name;
                mySA.SelectCommand.Parameters["@last_name"].Value = customer.last_name;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);


                customers.customer_id = Convert.ToInt32(myDS.Tables[0].Rows[0]["customer_id"]);

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }



            return customers;


        }

        /*
         Method: GetCstemerDetail 
         * Pram1: id : user id 
         * Pram2: error : error: coming from the BL part make sure that there is not error input
         */


        public static BaseCustomer GetCustomerDetail(int id, ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            BaseCustomer customer = null;

            try
            {
                string strSQL = "get_customer_details";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));

                mySA.SelectCommand.Parameters["@id"].Value = id;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                if (myDS.Tables[0].Rows.Count == 0)
                    return null;

                customer = new BaseCustomer();
                customer.customer_id = Convert.ToInt32(myDS.Tables[0].Rows[0]["customer_id"]);
                customer.email = myDS.Tables[0].Rows[0]["email"].ToString();
                customer.first_name = myDS.Tables[0].Rows[0]["first_name"].ToString();
                customer.last_name = myDS.Tables[0].Rows[0]["last_name"].ToString();
                customer.address = myDS.Tables[0].Rows[0]["address"].ToString();
                customer.state = myDS.Tables[0].Rows[0]["state"].ToString();
                customer.zip = Convert.ToInt32(myDS.Tables[0].Rows[0]["zip"].ToString());
                customer.city = myDS.Tables[0].Rows[0]["city"].ToString();
                customer.webmaster = Convert.ToInt32(myDS.Tables[0].Rows[0]["webmaster"]);
                customer.blockuser = Convert.ToInt32(myDS.Tables[0].Rows[0]["blockuser"]);

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }

            return customer;
        }


        //This procedure deletes a customer
        /*parm: id: trager that we want to be able or disable webmaster fucntiuon
         *     Deleting user by user id
         */
        public static void DeleteCustomer(int id, ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);

            try
            {
                string strSQL = "delete_customer";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@customer_id", SqlDbType.Int));

                mySA.SelectCommand.Parameters["@customer_id"].Value = id;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);


            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
        }

        /*Method name GetEmail
         parm: id: trager that we want to be able or disable webmaster fucntiuon
         * 
         * getting Customer's email by user id.
         */
        public static String GetEmail(int id, ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);

            String returnString = "";
            try
            {
                string strSQL = "get_customer_email";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)); // declear var for query
                mySA.SelectCommand.Parameters["@id"].Value = id; // assinging var to the @id
                DataSet myDS = new DataSet();
                mySA.Fill(myDS);
                if (myDS.Tables[0].Rows.Count == 0)
                    return null;

                returnString = myDS.Tables[0].Rows[0]["email"].ToString();



            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
            return returnString;
        }


        //get id from customer email
        public static int GetIdFromEmail(string email, ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);

            int returnid = 0;
            try
            {
                string strSQL = "email_to_id";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@email", SqlDbType.VarChar)); // declear var for query
                mySA.SelectCommand.Parameters["@email"].Value = email; // assinging var to the @id
                DataSet myDS = new DataSet();
                mySA.Fill(myDS);
                if (myDS.Tables[0].Rows.Count == 0)
                    return -1;

                returnid = Convert.ToInt32(myDS.Tables[0].Rows[0]["customer_id"]);



            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
            return returnid;
        }


        /*Method name GetName
        parm: id: trager that we want to be able or disable webmaster fucntiuon 
        * getting Customer's Name by user id.
        */

        public static List<BaseCustomer> GetName(ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);

            //     int elementSize = 0;
            BaseCustomer customer = null;
            List<BaseCustomer> UserInfoList = new List<BaseCustomer>();
            try
            {
                string strSQL = "get_customer_names";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataSet myDS = new DataSet();
                mySA.Fill(myDS);
                if (myDS.Tables[0].Rows.Count == 0)
                    return null;



                for (int index = 0; index < myDS.Tables[0].Rows.Count; index++)
                {
                    customer = new BaseCustomer();
                    customer.customer_id = Convert.ToInt32(myDS.Tables[0].Rows[index]["customer_id"]);
                  
                   customer.email = myDS.Tables[0].Rows[index]["email"].ToString();
                    customer.first_name = myDS.Tables[0].Rows[index]["first_name"].ToString();
                    customer.last_name = myDS.Tables[0].Rows[index]["last_name"].ToString();
                    customer.address = myDS.Tables[0].Rows[index]["address"].ToString();
                    customer.state = myDS.Tables[0].Rows[index]["state"].ToString();
                    customer.zip = Convert.ToInt32(myDS.Tables[0].Rows[index]["zip"]);
                    customer.city = myDS.Tables[0].Rows[index]["city"].ToString();
                    customer.webmaster = Convert.ToInt32(myDS.Tables[0].Rows[index]["webmaster"]);
                    customer.blockuser = Convert.ToInt32(myDS.Tables[0].Rows[index]["blockuser"]);

                    
                    UserInfoList.Add(customer);
                }

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
            return UserInfoList;
        }


        /*GetBlockUserInfor
         * For Webmaster page. it will link with other page that showing 
         *  information of block users.
         */


        public static List<BaseCustomer> GetBlockUserInfor(ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);

            //     int elementSize = 0;
            BaseCustomer customer = null;
            List<BaseCustomer> UserInfoList = new List<BaseCustomer>();
            try
            {
                string strSQL = "get_total_block_user";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataSet myDS = new DataSet();
                mySA.Fill(myDS);
                if (myDS.Tables[0].Rows.Count == 0)
                    return null;



                for (int index = 0; index < myDS.Tables[0].Rows.Count; index++)
                {
                    customer = new BaseCustomer();
                    customer.customer_id = Convert.ToInt32(myDS.Tables[0].Rows[index]["customer_id"]);

                    customer.email = myDS.Tables[0].Rows[index]["email"].ToString();
                    customer.first_name = myDS.Tables[0].Rows[index]["first_name"].ToString();
                    customer.last_name = myDS.Tables[0].Rows[index]["last_name"].ToString();
                    customer.address = myDS.Tables[0].Rows[index]["address"].ToString();
                    customer.state = myDS.Tables[0].Rows[index]["state"].ToString();
                    customer.zip = Convert.ToInt32(myDS.Tables[0].Rows[index]["zip"]);
                    customer.city = myDS.Tables[0].Rows[index]["city"].ToString();
                    customer.webmaster = Convert.ToInt32(myDS.Tables[0].Rows[index]["webmaster"]);
                    customer.blockuser = Convert.ToInt32(myDS.Tables[0].Rows[index]["blockuser"]);


                    UserInfoList.Add(customer);
                }

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
            return UserInfoList;
        }


        /*GetWebMasterInfor
         * For Webmaster page. it will link with other page that showing 
         *  information of all webmaster information.
         */


        public static List<BaseCustomer> GetWebMasterInfor(ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);

            //     int elementSize = 0;
            BaseCustomer customer = null;
            List<BaseCustomer> UserInfoList = new List<BaseCustomer>();
            try
            {
                string strSQL = "get_total_webmaster_user";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataSet myDS = new DataSet();
                mySA.Fill(myDS);
                if (myDS.Tables[0].Rows.Count == 0)
                    return null;



                for (int index = 0; index < myDS.Tables[0].Rows.Count; index++)
                {
                    customer = new BaseCustomer();
                    customer.customer_id = Convert.ToInt32(myDS.Tables[0].Rows[index]["customer_id"]);

                    customer.email = myDS.Tables[0].Rows[index]["email"].ToString();
                    customer.first_name = myDS.Tables[0].Rows[index]["first_name"].ToString();
                    customer.last_name = myDS.Tables[0].Rows[index]["last_name"].ToString();
                    customer.address = myDS.Tables[0].Rows[index]["address"].ToString();
                    customer.state = myDS.Tables[0].Rows[index]["state"].ToString();
                    customer.zip = Convert.ToInt32(myDS.Tables[0].Rows[index]["zip"]);
                    customer.city = myDS.Tables[0].Rows[index]["city"].ToString();
                    customer.webmaster = Convert.ToInt32(myDS.Tables[0].Rows[index]["webmaster"]);
                    customer.blockuser = Convert.ToInt32(myDS.Tables[0].Rows[index]["blockuser"]);


                    UserInfoList.Add(customer);
                }

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
            return UserInfoList;
        }
        /*Method name Updata use address
     parm: id: trager that we want to be able or disable webmaster fucntiuon 
     * getting Customer's Name by user id.

     */
        public static void UpdateUserReden(int id, BaseCustomer UpdateList, ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);

            try
            {
                string strSQL = "update_customer_residence";


                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.VarChar, 50));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@address", SqlDbType.VarChar, 50));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@state", SqlDbType.VarChar, 50));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@zip", SqlDbType.Int));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@city", SqlDbType.VarChar, 50));


                mySA.SelectCommand.Parameters["@id"].Value = id;
                mySA.SelectCommand.Parameters["@address"].Value = UpdateList.address;
                mySA.SelectCommand.Parameters["@state"].Value = UpdateList.state;
                mySA.SelectCommand.Parameters["@zip"].Value = UpdateList.zip;
                mySA.SelectCommand.Parameters["@city"].Value = UpdateList.city;



                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

            }
            catch (Exception e)
            {

                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
        }


        public static BaseCustomer LoginInCheck(String Email, String firstName, ref List<string> errors) 
        {

            SqlConnection conn = new SqlConnection(connection_string);
            BaseCustomer LoginInfor = new BaseCustomer();

            try
            {
                string strSQL = "login_check";
                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@firstname", SqlDbType.VarChar, 50));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@email", SqlDbType.VarChar, 50));

                mySA.SelectCommand.Parameters["@firstname"].Value = firstName;
                mySA.SelectCommand.Parameters["@email"].Value = Email;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                LoginInfor.email = myDS.Tables[0].Rows[0]["email"].ToString();
                LoginInfor.first_name = myDS.Tables[0].Rows[0]["first_name"].ToString();
                LoginInfor.webmaster = Convert.ToInt32(myDS.Tables[0].Rows[0]["webmaster"]);
                LoginInfor.blockuser = Convert.ToInt32(myDS.Tables[0].Rows[0]["blockuser"]);

            }
            catch (Exception e)
            {

                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }

            return LoginInfor;

        }

        public static int GetTotalUser (ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            int total_num = 0;

            try
            {
                string strSQL = "get_total_customer";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;              

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                if (myDS.Tables[0].Rows.Count == 0)
                    return 0;


                total_num = Convert.ToInt32(myDS.Tables[0].Rows[0]["total_users"]);
               

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }

            return total_num;
        }
        public static int GetTotalWebMaster(ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            int total_num = 0;

            try
            {
                string strSQL = "get_total_webmaster";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                if (myDS.Tables[0].Rows.Count == 0)
                    return 0;


                total_num = Convert.ToInt32(myDS.Tables[0].Rows[0]["web"]);


            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }

            return total_num;
        }
      
        public static int GetTotalBlock(ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            int total_num = 0;

            try
            {
                string strSQL = "get_total_blockuser";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                if (myDS.Tables[0].Rows.Count == 0)
                    return 0;


                total_num = Convert.ToInt32(myDS.Tables[0].Rows[0]["ppl"]);


            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }

            return total_num;
        }

      



        
    }

}